﻿using System;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DAL
{
    public class DAL_Customer
    {
        public const string usp_SelectCustomersAll         = "usp_SelectCustomersAll";
        public const string usp_SelectCustomerByEmailPhone = "usp_SelectCustomerByEmailPhone";
        public const string usp_DeleteCustomer             = "usp_DeleteCustomer";

        public DataTable Get_Customer_All()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand = new SqlCommand(usp_SelectCustomersAll, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                return dt;
            }
            return dt;
        }

        public DataTable Get_Customer_Email_Phone(string pEmail, string pPhone)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sqlDataAdapter;
            SqlParameter[] sqlParameter = new SqlParameter[2];
            SqlCommand sqlCommand = new SqlCommand(usp_SelectCustomerByEmailPhone, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.String;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@EMAIL";
            sqlParameter[0].Value = pEmail;

            sqlParameter[1] = new SqlParameter();
            sqlParameter[1].DbType = DbType.String;
            sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[1].ParameterName = "@PHONE";
            sqlParameter[1].Value = pPhone;

            sqlCommand.Parameters.AddRange(sqlParameter);
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            try
            {
                Utils.sqlConnection.Open();
                sqlDataAdapter.Fill(dt);
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return dt;
            }
            return dt;
        }
        
        public bool DeleteCustomer(Int64 pID)
        {
            SqlParameter[] sqlParameter = new SqlParameter[1];
            SqlCommand sqlCommand = new SqlCommand(usp_DeleteCustomer, Utils.sqlConnection);
            sqlCommand.CommandType = CommandType.StoredProcedure;

            sqlParameter[0] = new SqlParameter();
            sqlParameter[0].DbType = DbType.Int64;
            sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[0].ParameterName = "@ID";
            sqlParameter[0].Value = pID;
            sqlCommand.Parameters.AddRange(sqlParameter);
            try
            {
                Utils.sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                Utils.sqlConnection.Close();
            }
            catch
            {
                if (Utils.sqlConnection.State == ConnectionState.Open)
                {
                    Utils.sqlConnection.Close();
                }
                return false;
            }
            return true;
        }
    }
}
